
# pip install openpyxl==3.1.2
from openpyxl import Workbook, load_workbook
from tkinter import filedialog

"""
class txt_tools(object):
    def __init__(self,txt_file):
         self.f2=open(txt_file, 'a', encoding='UTF-8')

    def MakeFolder1(self):
        self.f2.write('<Style id="cqt">'+'\n')
        self.f2.write('<IconStyle>'+'\n')
        self.f2.write('<color>e5ff0000</color><scale>0.8</scale>'+'\n')
        self.f2.write('<Icon><href>http://maps.google.com/mapfiles/kml/shapes/shaded_dot.png</href></Icon>'+'\n')
        self.f2.write('</IconStyle>'+'\n')
        self.f2.write('<LabelStyle><color>e580ffff</color></LabelStyle>'+'\n')
        self.f2.write('</Style>'+'\n')
        self.f2.write('\n')

    def MakeFolder2(self):
        self.f2.write('<Style id="25516776960">'+'\n')
        self.f2.write('<PolyStyle><color>FF00FFFF</color></PolyStyle>'+'\n')
        self.f2.write('<LineStyle><color>FF00FFFF</color><width>3</width></LineStyle>'+'\n')
        self.f2.write('</Style>'+'\n')
        self.f2.write('\n')

    def MakeFolder3(self):
        self.f2.write('<Style id="mycat">'+'\n')
        self.f2.write('<IconStyle>')
        self.f2.write('<color>FF00FFFF</color><scale>1.5</scale>'+'\n')
        self.f2.write('<Icon><href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href></Icon>'+'\n')
        self.f2.write('</IconStyle>'+'\n')
        self.f2.write('<LabelStyle><color>e580ffff</color></LabelStyle>'+'\n')
        self.f2.write('</Style>'+'\n')
        self.f2.write('\n')

    def MakePlacemark1(self,Place1):
        self.f2.write('<Placemark>'+'\n')
        self.f2.write('<name>' + ThisWorkbook.Sheets("拉线图制作").Cells(Place1, 6) + '</name>'+'\n')
        'self.f2.write ('<name>' + Str(ThisWorkbook.Sheets("拉线图制作").Cells(Place1, 9)) + '米' + '</name>'+'\n')
        self.f2.write('<styleUrl>#cqt</styleUrl>'+'\n')
        self.f2.write('<Point>'+'\n')
        a = ThisWorkbook.Sheets("拉线图制作").Cells(Place1, 7)
        b = ThisWorkbook.Sheets("拉线图制作").Cells(Place1, 8)
        c = a + "," + b + ",0 "
        self.f2.write('<coordinates>' + c + '</coordinates>'+'\n')
        self.f2.write('</Point>'+'\n')
        self.f2.write('\n')
        a = ""
        b = ""
        c = ""

    def MakePlacemark2(self,Place2):
        self.f2.write('<Placemark>'+'\n')
        self.f2.write('<name><![CDATA[" + ThisWorkbook.Sheets("拉线图制作").Cells(Place2, 1) + "]]></name>'+'\n')
        self.f2.write('<styleUrl>#mycat</styleUrl>'+'\n')
        self.f2.write('<Point>'+'\n')
        a = ThisWorkbook.Sheets("拉线图制作").Cells(Place2, 2)
        b = ThisWorkbook.Sheets("拉线图制作").Cells(Place2, 3)
        c = a + "," + b + ",0 "
        self.f2.write('<coordinates>' + c + '</coordinates>'+'\n')
        self.f2.write('</Point>'+'\n')
        self.f2.write('\n')
        a = ""
        b = ""
        c = ""

    def MakeDescription1(self,Desc1):
        self.f2.write('<description>'+'\n')
        self.f2.write('<![CDATA['+'\n')
        self.f2.write('<table width="400" border="2">'+'\n')
        self.f2.write('<tr>'+'\n')
        self.f2.write('<td width="2%"><p align="middle">'+'\n')
        self.f2.write('<name>' + '测试点名' + '</name>'+'\n')
        self.f2.write('</td>'+'\n')
        self.f2.write('<td width="6%"><p align="middle">'+'\n')
        self.f2.write('<name>' + ThisWorkbook.Sheets("拉线图制作").Cells(Desc1, 6) + '</name>'+'\n')
        self.f2.write('</tr>'+'\n')
        self.f2.write('</table>'+'\n')
        self.f2.write('<p>'+'\n')
        self.f2.write('<a href="mailto:XXXX?subject=关于规划点与测试点图层的问题">联系作者(XXX)</a>'+'\n')
        self.f2.write('</p>'+'\n')
        self.f2.write(']]>'+'\n')
        self.f2.write('</description>'+'\n')
        self.f2.write('</Placemark>'+'\n')
        self.f2.write('\n')

    def MakeDescription2(self,Desc2):
        self.f2.write ('<description>'+'\n')
        self.f2.write ('<![CDATA['+'\n')
        self.f2.write ('<table width="400" border="2">'+'\n')
        self.f2.write ('<tr>'+'\n')
        self.f2.write ('<td width="2%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + '站名' + '</name>'+'\n')
        self.f2.write ('</td>'+'\n')
        self.f2.write ('<td width="6%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + ThisWorkbook.Sheets("拉线图制作").Cells(Desc2, 1) + '</name>'+'\n')
        self.f2.write ('</tr>'+'\n')
        self.f2.write ('<tr>'+'\n')
        self.f2.write ('<td width="2%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + '方位角' + '</name>'+'\n')
        self.f2.write ('</td>'+'\n')
        self.f2.write ('<td width="6%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + ThisWorkbook.Sheets("拉线图制作").Cells(Desc2, 4) + '</name>'+'\n')
        self.f2.write ("</tr>"+'\n')
        self.f2.write ('<tr>'+'\n')
        self.f2.write ('<td width="2%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + '经度' + '</name>'+'\n')
        self.f2.write ('</td>'+'\n')
        self.f2.write ('<td width="6%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + Str(ThisWorkbook.Sheets("拉线图制作").Cells(Desc2, 2)) + '</name>'+'\n')
        self.f2.write ('</tr>'+'\n')
        self.f2.write ('<tr>'+'\n')
        self.f2.write ('<td width="2%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + '纬度' + '</name>'+'\n')
        self.f2.write ('</td>'+'\n')
        self.f2.write ('<td width="6%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + Str(ThisWorkbook.Sheets("拉线图制作").Cells(Desc2, 3).Value) + '</name>'+'\n')
        self.f2.write ('</tr>'+'\n')
        self.f2.write ('</table>'+'\n')
        self.f2.write ('<p>'+'\n')
        self.f2.write ('<a href="mailto:XXXX?subject=关于规划点与测试点图层的问题">联系作者(XXX)</a>'+'\n')
        self.f2.write ('</p>'+'\n')
        self.f2.write (']]>'+'\n')
        self.f2.write ('</description>'+'\n')
        self.f2.write ('</Placemark>'+'\n')
        self.f2.write ('\n')

    def MakeKML2(self):
        self.f2.write('<?xml version="1.0" encoding="utf-8"?>'+'\n')
        self.f2.write('<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom">'+'\n')
        ##============================================写测试点========================================
        self.f2.write('<Document>'+'\n')
        self.f2.write('<name>规划站点与投诉</name>'+'\n')
        self.f2.write('<Folder>'+'\n')
        self.f2.write('<name>测试点</name>'+'\n')
        self.MakeFolder1()

        For Each SitName In ThisWorkbook.Sheets("拉线图制作").Range("f2:f" & ThisWorkbook.Sheets("拉线图制作").Cells(ThisWorkbook.Sheets("拉线图制作").Rows.Count, "f").End(xlUp).Row):
            if SitName is not None:
                Ts = SitName.Row
                self.MakePlacemark1(Ts)
                self.MakeDescription1(Ts)

        self.f2.write('</Folder>'+'\n')
        ##==============================================写规划点========================================
        self.f2.write('<Folder>'+'\n')
        self.f2.write('<name>规划点</name>'+'\n')
        self.MakeFolder3()
        For Each SitName In ThisWorkbook.Sheets("拉线图制作").Range("a2:a" & ThisWorkbook.Sheets("拉线图制作").Cells(ThisWorkbook.Sheets("拉线图制作").Rows.Count, 1).End(xlUp).Row):
            if SitName is not None:
                ro = Val(SitName.Row)
                self.MakePlacemark2(ro)
                self.MakeDescription2(ro)
        self.f2.write('</Folder>'+'\n')
        ##=============================================写拉线==============================================
        self.f2.write('<Folder>'+'\n')
        self.f2.write('<name>拉线</name>'+'\n')
        self.MakeFolder2()

        For Each SitName In ThisWorkbook.Sheets("拉线图制作").Range("a2:a" & ThisWorkbook.Sheets("拉线图制作").Cells(ThisWorkbook.Sheets("拉线图制作").Rows.Count, 1).End(xlUp).Row):
            if SitName is not None:
                self.f2.write('<Placemark>'+'\n')
                self.f2.write('<name>'+'\n')
                self.f2.write('<![CDATA[' + SitName.Value + ']]>'+'\n')
                self.f2.write('</name>'+'\n')
                self.f2.write('<styleUrl>#25516776960</styleUrl>'+'\n')
                self.f2.write('<LineString>'+'\n')
                self.f2.write('<coordinates>'+'\n')
                For Ts = SitName.Row To(SitName.Row + Val(ThisWorkbook.Sheets("拉线图制作").Cells(SitName.Row, 5).Value) - 1):
                    a = ThisWorkbook.Sheets("拉线图制作").Cells(Ts, 7)
                    b = ThisWorkbook.Sheets("拉线图制作").Cells(Ts, 8)
                    c = ThisWorkbook.Sheets("拉线图制作").Cells(SitName.Row, 2)
                    d = ThisWorkbook.Sheets("拉线图制作").Cells(SitName.Row, 3)
                    e = a + "," + b + ",0 " + c + "," + d
                    self.f2.write(e)
                    a = ""
                    b = ""
                    c = ""
                    d = ""
                    e = ""
                self.f2.write('</coordinates>'+'\n')
                self.f2.write('</LineString>'+'\n')
                self.f2.write('</Placemark>'+'\n')

        self.f2.write('</Folder>'+'\n')
        self.f2.write('</Document>'+'\n')
        self.f2.write('</kml>'+'\n')

        self.f2.close()
        print("生成完成！")

class excel_tools:
    def __init__(self):
         self.excel_file= filedialog.askopenfilename(title='导入EXCEL', filetypes=[('Excel', '*.xlsx')])

    @property
    def open_excel(self):
        workbook = load_workbook(self.excel_file)
        return workbook

    def read_all(self, sheet_name):
   
        wb = self.open_excel
        ws = wb[sheet_name]
        myRows = list(ws.values)[1:]

        all_values = []
        global guihua_tousu_values
        global tousu_dict
        global tousu_values
        global guihua_dics
        global tousu_count
        global site_key
        n=0
        # for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1,max_col=8):
        for myrow in myRows:
            if not myrow[0] is None:
                guihua_tousu_values = []   # 规划，投诉，数据规划成一个列表，作为规划站名（key），对应的值（value）
                guihua_values = []  # 规划站点的经纬度，方位角，规划成一个列表
                tousu_dict={}      # 每一个投诉规划一个字典，投诉名+经纬度
                tousu_values = []    # 所有投诉，规划一个投诉列表
                guihua_dics = {}         # 一个规划站点+n个投诉是一个字典，先置空
                tousu_count = myrow[4]     # 投诉数量
                site_key = myrow[0]            # 规划站名

                guihua_jwd_values=str(myrow[1]) + ',' + str(myrow[2])
                guihua_values.append(guihua_jwd_values)
                guihua_values.append(myrow[3])

                tousu_jwd_values = str(myrow[6]) + ',' + str(myrow[7])
                tousu_dict[myrow[5]] = tousu_jwd_values
                tousu_values.append(tousu_dict)

                guihua_tousu_values.append(guihua_values)
            else:
                tousu_dict = {}
                n = n + 1
                tousu_jwd_values=str(myrow[6]) + ',' + str(myrow[7])
                tousu_dict[myrow[5]] = tousu_jwd_values
                tousu_values.append(tousu_dict)
                if n+1 == tousu_count:
                    n=0
                    guihua_tousu_values.append(tousu_values)
                    guihua_dics[site_key] = guihua_tousu_values
                    all_values.append(guihua_dics)

        return all_values
"""
if __name__ == '__main__':

    tt = txt_tools(txt_file="拉线图.kml")
    # tt.MakeFolder1()
    tt.MakeKML2()

    et = excel_tools()
    all_values=et.read_all('数据模板')
    # print(all_values)
    for guihau_site_dics in all_values:
        for guihau_site_name,guihua_values in guihau_site_dics.items():
            print(guihau_site_name)    # 规划名字
            print(guihua_values[0][0])    # 规划经纬度  ['121.338118,28.583169', None]
            print(guihua_values[0][1])  # 规划方位角
            # print(guihua_values[1])  # 投诉字典列表 [{'台州路桥新盛佳苑-A': '121.339109,28.583257'}, {'台州路桥新盛佳苑-B': '121.338116,28.583093'}]
            for tousu_dic in guihua_values[1]:
                for k ,v in tousu_dic.items():
                    print(k)     # 投诉名
                    print(v)    # 投诉经纬度









